﻿using PSI.Helper;
using PSI.Models.TMS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;

namespace PSI.Services.TMS {
    public class TruckService : PSIBaseService {
        public List<TruckCompany> GetTruckCompanyList() {
            var result = new List<TruckCompany>();
            var sql = "select id, f_name,v_owner,n_tel,i_weixinStatus from tms_truck_company order by f_name";
            Query(sql, reader => {
                result.Add(new TruckCompany {
                    Id = reader.GetString(0),
                    Name = reader.GetString(1),
                    owner = reader.GetString(2),
                    tel = reader.GetString(3),
                    WeixinStatus=reader.GetInt32(4),
                });
            });
            return result;
        }

        public List<Truck> GetTruckList(string parentId) {
            var result = new List<Truck>();
            var sql = @"select id, f_truck_num, f_driver, f_driver_tel 
                        from tms_truck where parent_id = @parentId
                        order by f_truck_num";
            var p = CreateDbParameters();
            p.AddWithValue("parentId", parentId);
            Query(sql, p, reader => {
                result.Add(new Truck {
                    Id = reader.GetString(0),
                    TruckNumber = reader.GetString(1),
                    Driver = reader.GetString(2),
                    DriverTel = reader.GetString(3)
                });
            });
            return result;
        }

        public Result EditTruckCompnay(TruckCompany truck) {
            if (string.IsNullOrEmpty(truck.Id))
            {
                // 新增

                // 检查拖车公司是否已经存在
                var sql = "select count(*) from tms_truck_company where f_name = @name";
                var p = CreateDbParameters();
                p.AddWithValue("name", truck.Name);
                var cnt = QueryCount(sql, p);
                if (cnt > 0) {
                    return new Result { Success = false, Messgae = "拖车公司[" + truck.Name + "]已经存在" };
                }

                truck.Id = IdGeneratorService.NewId();

                sql = "insert into tms_truck_company(id, f_name, f_pinyin,v_owner,n_tel,v_password) values (@id, @name, @pinyin,@owner,@tel,@password)";
                p = CreateDbParameters();
                p.AddWithValue("id", truck.Id);
                p.AddWithValue("name", truck.Name);
                p.AddWithValue("pinyin", new PinYinHelper().ConvertToPinYinZiTou(truck.Name, true));
                p.AddWithValue("owner", truck.owner);
                p.AddWithValue("tel", truck.tel);
                p.AddWithValue("password", string.IsNullOrEmpty(truck.password)?"":SimpleCrypt.Encode(truck.password));
                Execute(sql, p);
            } else {
                // 编辑

                // 检查拖车公司是否已经存在
                var sql = "select * from tms_truck_company where f_name = @name and id= @id";
                var p = CreateDbParameters();
                p.AddWithValue("name",truck.Name);
                p.AddWithValue("id", truck.Id);
                var trucks = new TruckCompany();
                Query(sql, p, ready => {
                    trucks.password = ready.GetString(ready.GetOrdinal("v_password"));
                });


                sql = "update tms_truck_company set f_name = @name, f_pinyin = @pinyin,v_owner=@owner,n_tel=@tel,v_password=@password where id = @id";
                p = CreateDbParameters();
                p.AddWithValue("id", truck.Id);
                p.AddWithValue("name", truck.Name);
                p.AddWithValue("pinyin", new PinYinHelper().ConvertToPinYinZiTou(truck.Name, true));
                p.AddWithValue("owner",truck.owner);
                p.AddWithValue("tel",truck.tel);
                p.AddWithValue("password", string.IsNullOrEmpty(trucks.password) ?  SimpleCrypt.Encode("123456"):trucks.password );
                Execute(sql, p);
            }

            return new Result { Success = true, Id = truck.Id };
        }

        public Result EditTruck(Truck truck) {
            if (string.IsNullOrEmpty(truck.Id)) {
                // 新增

                //检查车号是否已经存在
                var sql = "select count(*) from tms_truck where f_truck_num = @truckNumber and parent_id = @parentId";
                var p = CreateDbParameters();
                p.AddWithValue("truckNumber", truck.TruckNumber);
                p.AddWithValue("parentId", truck.ParentId);
                var cnt = QueryCount(sql, p);
                if (cnt > 0) {
                    return new Result { Success = false, Messgae = "车号[" + truck.TruckNumber + "]已经存在" };
                }

                truck.Id = IdGeneratorService.NewId();

                sql = @"insert into tms_truck 
                        (id, parent_id, f_truck_num, f_truck_num_pinyin, f_driver, f_driver_pinyin, f_driver_tel) 
                        values (@id, @parentId, @truckNumber, @truckNumberPinYin, @driver, @driverPinYin, @driverTel)";
                p = CreateDbParameters();
                p.AddWithValue("id", truck.Id);
                p.AddWithValue("parentId", truck.ParentId);
                p.AddWithValue("truckNumber", truck.TruckNumber);
                p.AddWithValue("truckNumberPinYin", "qwe");
                p.AddWithValue("driver", truck.Driver);
                p.AddWithValue("driverPinYin","qwe");
                p.AddWithValue("driverTel", truck.DriverTel);
        

                Execute(sql, p);
            } else {
                // 编辑

                //检查车号是否已经存在
                var sql = "select count(*) from tms_truck where f_truck_num = @truckNumber and parent_id = @parentId and id <> @id";
                var p = CreateDbParameters();
                p.AddWithValue("truckNumber", truck.TruckNumber);
                p.AddWithValue("parentId", truck.ParentId);
                p.AddWithValue("id", truck.Id);
                var cnt = QueryCount(sql, p);
                if (cnt > 0) {
                    return new Result { Success = false, Messgae = "车号[" + truck.TruckNumber + "]已经存在" };
                }

                sql = @"update tms_truck
                            set f_truck_num = @truckNumber, f_truck_num_pinyin = @truckNumberPinYin,
                                f_driver = @driver, f_driver_pinyin = @driverPinYin, f_driver_tel = @driverTel,
                                where id = @id";
                           
                p = CreateDbParameters();
                p.AddWithValue("id", truck.Id);
                p.AddWithValue("truckNumber", truck.TruckNumber);
                p.AddWithValue("truckNumberPinYin", new PinYinHelper().ConvertToPinYinZiTou(truck.TruckNumber, true));
                p.AddWithValue("driver", truck.Driver);
                p.AddWithValue("driverPinYin", new PinYinHelper().ConvertToPinYinZiTou(truck.Driver, true));
                p.AddWithValue("driverTel", truck.DriverTel);
                Execute(sql, p);

            }

            return new Result { Success = true, Id = truck.Id };
        }

        public List<TruckCompany> QueryTruckCompany(string queryKey) {
            var result = new List<TruckCompany>();
            var sql = @"select top 50 id,f_name from tms_truck_company
                        where (f_name like @queryKey) or (f_pinyin like @queryKey)
                        order by f_name";
            var p = CreateDbParameters();
            p.AddWithValue("queryKey", "%" + queryKey + "%");
            Query(sql, p, reader => {
                result.Add(new TruckCompany {
                    Id = reader.GetString(0),
                    Name = reader.GetString(1)
                });
            });
            return result;
        }

        public List<Truck> QueryTruckNumber(string truckCompany, string queryKey) {
            var result = new List<Truck>();
            if (string.IsNullOrEmpty(truckCompany)) {
                var sql = @"select top 50 b.f_truck_num, b.f_driver, b.f_driver_tel, a.f_name 
                        from tms_truck b, tms_truck_company a
                        where (a.id = b.parent_id) and
                            ((b.f_truck_num like @queryKey) or (b.f_truck_num_pinyin like @queryKey))
                        order by b.f_truck_num";
                var p = CreateDbParameters();
                p.AddWithValue("queryKey", "%" + queryKey + "%");
                Query(sql, p, reader => {
                    result.Add(new Truck {
                        TruckNumber = reader.GetString(0),
                        Driver = reader.GetString(1),
                        DriverTel = reader.GetString(2),
                        TruckCompany = reader.GetString(3)
                    });
                });

            } else {
                var sql = @"select top 50 b.f_truck_num, b.f_driver, b.f_driver_tel 
                        from tms_truck_company a, tms_truck b
                        where (a.id = b.parent_id) and (a.f_name = @truckCompany)
                            and ((b.f_truck_num like @queryKey) or (b.f_truck_num_pinyin like @queryKey))
                        order by b.f_truck_num";
                var p = CreateDbParameters();
                p.AddWithValue("truckCompany", truckCompany);
                p.AddWithValue("queryKey", "%" + queryKey + "%");
                Query(sql, p, reader => {
                    result.Add(new Truck {
                        TruckNumber = reader.GetString(0),
                        Driver = reader.GetString(1),
                        DriverTel = reader.GetString(2)
                    });
                });
            }
            return result;
        }

        //重置密码
        public bool ReserPassWord(string id)
        {
            var originalpwd = new Helper.SymmetricMethod().Encrypto("123456");
            var sql = new StringBuilder("update tms_truck_company set v_password=@newpwd where id=@id");
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            p.AddWithValue("newpwd", originalpwd);
            int flag=Execute(sql.ToString(), p);
            if (flag > 0)
                return true;
            else
                return false;
        }
        //微信禁用
        public bool DisablePassWord(string id,string ff)
        {
            var sql = new StringBuilder("update tms_truck_company set i_weixinStatus=@status where id=@id");
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            p.AddWithValue("status", ff);
            int flag = Execute(sql.ToString(), p);
            if (flag > 0)
                return true;
            else
                return false;
        }
    }
}